﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DelonixRegiaSystem.Classes
{
    class StaffDBManager
    {
        public static ArrayList GetAll()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Delorix"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from staff";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    staff m = new staff();
                    m.staffNum = (int)dr["staffNum"];
                    m.staffName = (string)dr["staffName"];
                    m.staffType = (string)dr["staffType"];
                    m.dob = (string)dr["dob"];
                    m.bankNum = (string)dr["bankNum"];
                    m.homeAdd = (string)dr["homeAdd"];
                    m.phoneNum = (string)dr["phoneNum"];
                    m.dutyType = (string)dr["dutyType"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        // get staff by name
        public static staff GetStaffByStaffName(string staffName)
        {
            staff m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Delorix"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM staff WHERE staffName=@staffName";
                comm.Parameters.AddWithValue("@staffName", staffName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new staff();
                    m.staffNum = (int)dr["staffNum"];
                    m.staffName = (string)dr["staffName"];
                    m.staffType = (string)dr["staffType"];
                    m.dob = dr["dob"].ToString();
                    m.bankNum = (string)dr["bankNum"];
                    m.homeAdd = (string)dr["homeAdd"];
                    m.phoneNum = (string)dr["phoneNum"];
                    m.dutyType = (string)dr["dutyType"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static staff GetStaffByDuty(string dutyType)
        {
            staff m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Delorix"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM staff WHERE dutyType=@dutyType";
                comm.Parameters.AddWithValue("@dutyType", dutyType);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new staff();
                    m.staffNum = (int)dr["staffNum"];
                    m.staffName = (string)dr["staffName"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static staff GetStaffById(int staffNum)
        {
            staff m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Delorix"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM staff WHERE staffNum=@staffNum";
                comm.Parameters.AddWithValue("@staffNum", staffNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new staff();
                    m.staffNum = (int)dr["staffNum"];
                    m.staffName = (string)dr["staffName"];
                    m.staffType = (string)dr["staffType"];
                    m.dob = dr["dob"].ToString();
                    m.bankNum = (string)dr["bankNum"];
                    m.homeAdd = (string)dr["homeAdd"];
                    m.phoneNum = (string)dr["phoneNum"];
                    m.dutyType = (string)dr["dutyType"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static int InsertStaff(staff m)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Delorix"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO staff(staffName,staffType,dob,bankNum,homeAdd,phoneNum,dutyType)" +
                    " VALUES (@staffName,@staffType,@dob,@bankNum,@homeAdd,@phoneNum,@dutyType)";
                comm.Parameters.AddWithValue("@staffName", m.staffName);
                comm.Parameters.AddWithValue("@staffType", m.staffType);
                comm.Parameters.AddWithValue("@dob", m.dob);
                comm.Parameters.AddWithValue("@bankNum", m.bankNum);
                comm.Parameters.AddWithValue("@homeAdd", m.homeAdd);
                comm.Parameters.AddWithValue("@phoneNum", m.phoneNum);
                comm.Parameters.AddWithValue("@dutyType", m.dutyType);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static int UpdateStaff(staff m)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Delorix"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE staff SET staffName=@staffName,staffType=@staffType,dob=@dob,bankNum=@bankNum,homeAdd=@homeAdd,phoneNum=@phoneNum,dutyType=@dutyType where staffNum=@staffNum";
                comm.Parameters.AddWithValue("@staffNum", m.staffNum);
                comm.Parameters.AddWithValue("@staffName", m.staffName);
                comm.Parameters.AddWithValue("@staffType", m.staffType);
                comm.Parameters.AddWithValue("@dob", m.dob);
                comm.Parameters.AddWithValue("@bankNum", m.bankNum);
                comm.Parameters.AddWithValue("@homeAdd", m.homeAdd);
                comm.Parameters.AddWithValue("@phoneNum", m.phoneNum);
                comm.Parameters.AddWithValue("@dutyType", m.dutyType);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
        public static int DeleteStaff(int staffNum)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Delorix"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from staff where staffNum=@staffNum";
                comm.Parameters.AddWithValue("@staffNum", staffNum);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
    }
}
